package model.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import model.CommonValues;
import model.database.DBException;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class StoreDB implements Database {

	// connection information
	public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	public static final String DB_URL = "jdbc:mysql://localhost/";
	public static final String SERVER_NAME = "localhost";
	public static final String DB_NAME = "store_database";
	public static final String DB_USER = "root";
	public static final String DB_PWD = "";
	
	// database table names
	public static final String EMPLOYEES_TABLE = "Employees";
	public static final String PRODUCTS_TABLE = "Products";	
	public static final String ORDERS_TABLE = "Orders";
	public static final String CUSTOMERS_TABLE = "Customers";
	public static final String PASSWORDS_TABLE = "Passwords";
	
	// column names
	public static final String NAME = "name";
	public static final String NUMBER = "number";
	public static final String ORDERING_CUSTOMER_NUMBER = "ordering_customer_number";
	public static final String DATE = "date";
	public static final String TOTAL_SUM = "total_sum";
	public static final String PRICE = "price";
	public static final String AMOUNT = "amount";
	public static final String ADDRESS = "address";
	public static final String PAYMENT_METHOD = "payment_method";
		
	public static final String USER_NAME = "user_name";
	public static final String PASSWORD = "password";
	public static final String PERMISSION = "permission";
	
	public static final String DEFAULT_PWD = "qwerty"; // default password for a user
	
	private MysqlDataSource ds; // source for data
	
	/**
	 * Constructor- creates the database if it does not exist
	 * @throws DBException if had problems initializing the database
	 */
	public StoreDB() throws DBException {
		
		// connection to server
		Connection connection = null;
		
		try {
			
			// register JDBC driver
			Class.forName(JDBC_DRIVER);
			
			// open a connection
			connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
			
			// create the database
			Statement statement = connection.createStatement();
			statement.executeUpdate("CREATE DATABASE IF NOT EXISTS " + DB_NAME);
			
			// allocate a new data source
			ds = new MysqlConnectionPoolDataSource();
			
	    	// set server and database        	
	    	ds.setServerName(SERVER_NAME);
	    	ds.setDatabaseName(DB_NAME);
			
	    	// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			statement = connection.createStatement();

			// command to create passwords table			
			String command = String.format("CREATE TABLE IF NOT EXISTS `%s` " +
					"(`%s` VARCHAR(30) NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` INT NOT NULL, PRIMARY KEY (`%s`));",	
					PASSWORDS_TABLE, USER_NAME, PASSWORD, PERMISSION, USER_NAME); // TODO NOW ONLY USER_NAME IS PRIMARY KEY
			
			// create the table
			statement.executeUpdate(command);
			
			// command to create employees table
			command = String.format("CREATE TABLE IF NOT EXISTS `%s` " +
					"(`%s` INT NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` VARCHAR(30) NOT NULL, PRIMARY KEY (`%s`));",	
					EMPLOYEES_TABLE, NUMBER, NAME, USER_NAME, NUMBER);
			
			// create the table
			statement.executeUpdate(command);
			
			// command to create customers table
			command = String.format("CREATE TABLE IF NOT EXISTS `%s` " +
					"(`%s` INT NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` VARCHAR(60) NOT NULL, " +
					"`%s` INT NOT NULL, PRIMARY KEY (`%s`));",	
					CUSTOMERS_TABLE, NUMBER, NAME, USER_NAME, ADDRESS, PAYMENT_METHOD, NUMBER); 
			
			// create the table
			statement.executeUpdate(command);
			
			// command to create products table
			command = String.format("CREATE TABLE IF NOT EXISTS `%s` " +
					"(`%s` VARCHAR(30) NOT NULL, `%s` DOUBLE NOT NULL, PRIMARY KEY (`%s`));",	
					PRODUCTS_TABLE, NAME, PRICE, NAME);
			
			// create the table
			statement.executeUpdate(command);
			
			// command to create orders table
			command = String.format("CREATE TABLE IF NOT EXISTS `%s` " +
					"(`%s` INT NOT NULL, `%s` DATE NOT NULL, `%s` INT NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` INT NOT NULL, " +
					"`%s` DOUBLE NOT NULL, PRIMARY KEY (`%s`));",
					ORDERS_TABLE, NUMBER, DATE, ORDERING_CUSTOMER_NUMBER, NAME, AMOUNT, TOTAL_SUM, NUMBER);
						
			// create the table
			statement.executeUpdate(command);
			
		} catch (ClassNotFoundException e) {
			
			// database error			
			throw new DBException("Could not initialize system- database error!");
			
		} catch (SQLException e) {
			
			// database error			
			throw new DBException("Could not initialize system- database error!");
			
		} finally { 
			
			// try closing connection
			closeConnection(connection, "Could not initialize system- database error!");
		}		
	}

	/**
	 * Gets employee details and adds an employee to store database.
	 * New employee's type if defined by the permission.
	 * Default user name is employee's number, and password is set to default password.
	 * Cannot add employee if there is another employee record with given number.
	 * Throws DBException if had errors connecting to database
	 */
	@Override	
	public boolean addEmployee(int number, String name, String userName,
			CommonValues.permissions permission) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
		
		
			// create user record with given permission
			boolean couldAdd = addUserRecord(userName, permission);
			
			// check success
			if (!couldAdd) { return false; }
			
			// create a new employee record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					EMPLOYEES_TABLE, NUMBER, NAME, USER_NAME,
					number, name, userName);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not add record- delete user-name record
				query = String.format("DELETE FROM %s WHERE %s = '%s' AND %s = '%s'", 
						PASSWORDS_TABLE, USER_NAME, ""+number, PASSWORD, DEFAULT_PWD);
				
				// could not add employee
				return false;
			}
			
			// employee added successfully
			return true;
			
		} catch (MySQLIntegrityConstraintViolationException e) {
			
			// there is another record matching key value
			return false;
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}
	
	/**
	 * Creates a new account for customer matching given details.
	 * A new user-name record is created- default user name is customer's number.
	 * Cannot add customer if there is another customer record with given number.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public boolean createCustomerAccount(int number, String name, String userName,
			String address, CommonValues.paymentMethods paymentMethod)
			throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
				
			// create user-name and password
			boolean couldAdd = addUserRecord(userName, CommonValues.permissions.CUSTOMER);
			
			// check success
			if (!couldAdd) { return false; }
			
			// create a new customer record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s')",
					CUSTOMERS_TABLE, NUMBER, NAME, USER_NAME, ADDRESS, PAYMENT_METHOD,
					number, name, userName, address, paymentMethod.ordinal());
			
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not add record- delete user-name record
				query = String.format("DELETE FROM %s WHERE %s = '%s' AND %s = '%s'", 
						PASSWORDS_TABLE, USER_NAME, userName, PASSWORD, DEFAULT_PWD);
				
				// could not add employee
				return false;
			}
			
			// customer added successfully
			return true;
			
		} catch (MySQLIntegrityConstraintViolationException e) {
			
			// there is another record matching key value
			return false;
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}		
	}
	
	/**
	 * Adds a new record with given user-name, permission and default password.
	 * Cannot add user if there is another user record with given user-name, 
	 * password and permission.
	 * Throws DBException if had errors connecting to database
	 */
	@Override
	public boolean addUserRecord(String userName,
			CommonValues.permissions permission) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					PASSWORDS_TABLE, USER_NAME, PASSWORD, PERMISSION,
					userName, DEFAULT_PWD, permission.ordinal());
		
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not add record
				return false;
			}
			
			// user record added successfully
			return true;

		} catch (MySQLIntegrityConstraintViolationException e) {
			
			// there is another record matching key value
			return false;
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}
	
	/**
	 * Adds a new product with given name and price.
	 * Cannot add product if there is another product record with given name
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public boolean addNewProduct(String name, double price) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// create a product record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
					PRODUCTS_TABLE, NAME, PRICE,
					name, price);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not add product
				return false;
			}
			
			// product added successfully
			return true;
		
		} catch (MySQLIntegrityConstraintViolationException e) {
			
			// there is another record matching key value
			return false;
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}
	
	/**
	 * Adds a new order with given details. 
	 * Order's date is set to be current date.
	 * Cannot add order if there is another order record with given number,
	 * or if there is no product matching given name or no customer matching
	 * given number. 
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public boolean addNewOrder(int orderNumber, String productName, int amount,
			int customerNumber) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// get ordered product's price
			double price = getProductPrice(productName);
			
			// check if there is a customer with given number
			Map<String, String> customer = getCustomerByNumber(customerNumber);
			if (customer.isEmpty()) { return false; }			
						
			// create order record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s, %s) VALUES ('%s', CURDATE(), '%s', '%s', '%s', '%s')",
					ORDERS_TABLE, NUMBER, DATE, ORDERING_CUSTOMER_NUMBER, NAME, AMOUNT, TOTAL_SUM,
					orderNumber, customerNumber, productName, amount, amount*price);
			
			// NUMBER, DATE, ORDERING_CUSTOMER_NUMBER, NAME, AMOUNT, TOTAL_SUM
			
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not add product
				return false;
			}
			
			// product added successfully
			return true;
		
		} catch (MySQLIntegrityConstraintViolationException e) {
			
			// there is another record matching key value
			return false;
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}
	
	/**
	 * Returns customer with given number 
	 * @param number- customer number to search
	 * @return customer record matching given number
	 * @throws DBException if had problems connecting to database
	 */
	private Map<String, String> getCustomerByNumber(int number) throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s WHERE %s = %s",
				CUSTOMERS_TABLE, NUMBER, number);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.USER_NAME);
		keys.add(CommonValues.ADDRESS);
		keys.add(CommonValues.PAYMENT_METHOD);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		if (result.isEmpty()) { return new HashMap<String, String>(); }
		return result.get(0); 
	}
	
	/**
	 * Updates price of product with given name to given price.
	 * If there is no product with given name, returns False and
	 * no price is updated.
	 * Throws DBException if had problems connecting database
	 */
	@Override
	public boolean updateProductPrice(String name, double newPrice)
			throws DBException { 
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();

			// create query string to update product's price
			String query = String.format("UPDATE %s SET %s = '%s' WHERE %s = '%s'",
					PRODUCTS_TABLE, PRICE, newPrice, NAME, name);
		
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not update price
				return false;
			}
			
			// price updated successfully
			return true;
			
		} catch (SQLException e) {
			
			System.out.println("StoreDB.updateProductPrice(): exception!"); // TODO DEBUG PRINT!!!
						
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}
	
	/**
	 * Sets given details for customer matching given number.
	 * If there is no customer with given number, False is returned
	 * and no details are updated.
	 * Throws DBException if had problems connecting database
	 */
	@Override
	public boolean updateCustomerDetails(int number, String name, String address,
			CommonValues.paymentMethods paymentMethod) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
	
			// create query string to update product's price
			String query = String.format("UPDATE %s SET %s = '%s', " +
					"%s = '%s', %s = '%s' WHERE %s = '%s'",
					CUSTOMERS_TABLE, NAME, name, ADDRESS, address, PAYMENT_METHOD, 
					paymentMethod.ordinal(), NUMBER, number);
		
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not update price
				return false;
			}
			
			// customer details updated successfully
			return true;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			System.out.println("StoreDB.updateCustomerDetails(): exception!"); // TODO DEBUG PRINT!!!
						
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve apatments- database error!");
		}
	}

	/**
	 * Sets given password to given user. Password is set only if
	 * correct (old) password is given:
	 * composes MySQL query that changes password only if password value
	 * matching given user-name is given password
	 * Throws DBException if had problems connecting to database
	 */
	public boolean setPassword(String userName, String oldPassword, String newPassword)
			throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
		
			// create query string to change the password
			String query = String.format("UPDATE %s SET %s = '%s' WHERE %s = '%s' AND %s = '%s'",
					PASSWORD, PASSWORD, newPassword, USER_NAME, userName, PASSWORD, oldPassword);
		
			// execute query
			if (statement.executeUpdate(query) == 0) {
				
				// could not change password
				return false;
			}
			
			// password changed successfully
			return true;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			System.out.println("StoreDB.updateCustomerDetails(): exception!"); // TODO DEBUG PRINT!!!
						
			// database error
			throw new DBException("Could not change passworde- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not change passworde- database error!");
		}
	}
	
	/**
	 * Returns a list where each element is a map of product's parameters.
	 * Throws DBException if had problems connecting database 
	 */
	@Override
	public List<Map<String, String>> getProducts() throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s", PRODUCTS_TABLE);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.PRICE);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns price of the product with given name.
	 * Throws DBException if had problems connecting to database,
	 * or if product does not exists, or value stored as product's 
	 * price is not a numeric value 
	 */
	@Override
 	public double getProductPrice(String productName) throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s WHERE %s = '%s'", 
				PRODUCTS_TABLE, NAME, productName);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.PRICE);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// check if product exists
		if (result.isEmpty()) { 
			throw new DBException("No price with name: " + productName); 
		} 
		
		// extract numeric value
		double price = 0;
		try {
			
			price = Double.parseDouble(result.get(0).get(CommonValues.PRICE));
			
		} catch (NumberFormatException e) {
			throw new DBException("Could not get product price- database error!");
		}
		
		// return product's price
		return price;
	}
	
	/**
	 * Returns a list where each element is a map of product's parameters.
	 * Throws DBException if had problems connecting to database, 
	 */
	@Override
	public List<Map<String, String>> getEmployees() throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s", EMPLOYEES_TABLE);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.USER_NAME);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns a list where each element is a map of product's parameters.
	 * Throws DBException if had problems connecting to database 
	 */
	@Override
	public List<Map<String, String>> getCustomers() throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s", CUSTOMERS_TABLE);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.USER_NAME);
		keys.add(CommonValues.ADDRESS);
		keys.add(CommonValues.PAYMENT_METHOD);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns all customers that have ordered product matching given
	 * product name.
	 * Throws DBException if had problems connecting to database 
	 */
	@Override
	public List<Map<String, String>> getCustomers(String productName)
			throws DBException {
		
		// create query string
		String query = String.format(
				"SELECT %s,%s,%s,%s,%s " +
				"FROM %s,%s " +
				"WHERE %s = '%s' AND %s = %s",
				CUSTOMERS_TABLE + "." + NUMBER, CUSTOMERS_TABLE + "." + NAME,
				CUSTOMERS_TABLE + "." + USER_NAME, CUSTOMERS_TABLE + "."+ ADDRESS, 
				CUSTOMERS_TABLE + "." + PAYMENT_METHOD,
				ORDERS_TABLE, CUSTOMERS_TABLE,
				ORDERS_TABLE+"."+NAME, productName, ORDERS_TABLE+"."+ORDERING_CUSTOMER_NUMBER, CUSTOMERS_TABLE+"."+NUMBER);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.USER_NAME);
		keys.add(CommonValues.ADDRESS);
		keys.add(CommonValues.PAYMENT_METHOD);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns all customers that have ordered products in worth greater than
	 * given value.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public List<Map<String, String>> getCustomers(double totalSum)
			throws DBException {
		
		// create query string
		String query = String.format(
				"SELECT %s,%s,%s,%s,%s " +
				"FROM %s,%s " +
				"WHERE %s > %s AND %s = %s",
				CUSTOMERS_TABLE + "." + NUMBER, CUSTOMERS_TABLE + "." + NAME,
				CUSTOMERS_TABLE + "." + USER_NAME, CUSTOMERS_TABLE + "."+ ADDRESS, 
				CUSTOMERS_TABLE + "." + PAYMENT_METHOD,
				ORDERS_TABLE, CUSTOMERS_TABLE,
				ORDERS_TABLE+"."+TOTAL_SUM, totalSum, ORDERS_TABLE+"."+ORDERING_CUSTOMER_NUMBER, CUSTOMERS_TABLE+"."+NUMBER);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.USER_NAME);
		keys.add(CommonValues.ADDRESS);
		keys.add(CommonValues.PAYMENT_METHOD);

		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns orders made by customer matching given number.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public List<Map<String, String>> getOrders(int customerNumber)
			throws DBException {
		
		// create query string
		String query = String.format("SELECT * FROM %s WHERE %s = '%s'", 
				ORDERS_TABLE, ORDERING_CUSTOMER_NUMBER, customerNumber);
		
		// initialize key list
		List<String> keys = new ArrayList<String>();
		keys.add(CommonValues.NUMBER);
		keys.add(CommonValues.DATE);
		keys.add(CommonValues.ORDERING_CUSTOMER_NUMBER);
		keys.add(CommonValues.NAME);
		keys.add(CommonValues.AMOUNT);
		keys.add(CommonValues.TOTAL_SUM);
		
		// execute query
		List<Map<String, String>> result = retrieveData(query, keys);
		
		// return result in correct format
		return result;
	}
	
	/**
	 * Returns total sum of orders made on given date.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public double getSalesReport(String date) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// create query string
			String query = String.format("SELECT SUM(%s) FROM %s WHERE %s = '%s'",
					TOTAL_SUM, ORDERS_TABLE, DATE, date);
		
			// execute query
			ResultSet result = statement.executeQuery(query);
						
			// extract numeric value from result
			double salesReport = 0;
			if (result.next()) { salesReport = result.getDouble(1); }
			
			// return numeric value
			return salesReport;
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not get sales report for given date- database error!");
			
		} finally {
			
			// close connection
			closeConnection(connection, "Could not get sales report for given date- database error!");
		}
	}
	
	/**
	 * Returns total sum of orders made on given month.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public double getSalesReport(int month) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// create query string
			String query = String.format("SELECT SUM(%s) FROM %s WHERE MONTH(%s) = '%s'",
					TOTAL_SUM, ORDERS_TABLE, DATE, month);
		
			// execute query
			ResultSet result = statement.executeQuery(query);
						
			// extract numeric value from result
			double salesReport = 0;
			if (result.next()) { salesReport = result.getDouble(1); }
			
			// return numeric value
			return salesReport;
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not get sales report for given date- database error!");
			
		} finally {
			
			// close connection
			closeConnection(connection, "Could not get sales report for given date- database error!");
		}
	}
	
	/**
	 * Returns permission of each employee in system matching given user-names.
	 * Throws DBException if had problems connecting to database
	 */
	@Override
	public List<CommonValues.permissions> getEmployeesPermissions()
			throws DBException {

		// allocate list to hold user's permissions
		List<CommonValues.permissions> permissions = new LinkedList<CommonValues.permissions>();
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
				
			// get user-names of all employees
			// create query string
			String query = String.format("SELECT %s FROM %s", USER_NAME, EMPLOYEES_TABLE);
			
			// execute query
			ResultSet result = statement.executeQuery(query);
			
			// extract permission of each employee
			while (result.next()) {
				
				// get current employee's user-name
				String currUserName = result.getString(USER_NAME);
				
				// get permission of current employee
				CommonValues.permissions permission = getUserPermission(currUserName);
				
				// add permission
				permissions.add(permission);
			}
						
			// return user's permissions
			return permissions;
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not get employees permissions- database error!");
			
		} finally {
			
			// close connection
			closeConnection(connection, "Could not get employees permissions- database error!");
		}
	}
	
	private CommonValues.permissions getUserPermission(String userName) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
				
			// create query string
			String query = String.format("SELECT %s FROM %s WHERE %s = '%s'", 
					PERMISSION, PASSWORDS_TABLE, USER_NAME, userName);
			
			// execute query
			ResultSet result = statement.executeQuery(query);
			
			// extract permission of given user-name
			if (result.next()) {
				
				// get current user's permission
				int currUserPermission = result.getInt(PERMISSION);
				
				// check if value is valid
				if (currUserPermission < 0 || currUserPermission >= CommonValues.permissions.values().length) {
					
					// database error
					throw new DBException("Could not get user permission- database error!");
				}
				
				// return user's permission
				return CommonValues.permissions.values()[currUserPermission];
				
			} else {
				
				// user-name does not exist
				throw new DBException("Could not get user permission- user-name does not exist!");
			}
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not get user permission- database error!");
			
		} finally {
			
			// close connection
			closeConnection(connection, "Could not get user permission- database error!");
		}
	}
	
	/**
	 * Gets query that is meant to get data out of database,
	 * executes the query and returns its result 
	 * @param query- query to execute
	 * @return query's result
	 * @throws DBException if had problems connecting to database
	 */
	private List<Map<String, String>> retrieveData(String query, List<String> keys) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
	
			// convert data to parameters map
			return resultSetToMapList(resultSet, keys);
					
		} catch (SQLException e) {
			
			e.printStackTrace(); // TODO
			
			// database error
			throw new DBException("Could not retrieve data- database error!");
			
		} finally {
			
			// try closing connection
			closeConnection(connection, "Could not retrieve data- database error!");
		}
	}
	
	/**
	 * Gets ResultSet object holding data retrieved from database, and stores each row
	 * in a parameters. Parameters keys are set according to given keys list- key order
	 * is from first element to the last.
	 * Number of keys given should not be greater than number of columns in result set
	 * @param resultData- holds data retrieved from database 
	 * @param keys- keys for map holding row's value
	 * @return list holding each row's data as a map
	 * @throws DBException if had problems connecting to database
	 */
	private List<Map<String, String>> resultSetToMapList(ResultSet resultData,
			List<String> keys) throws DBException {
		
		// allocate list to hold converted data
		List<Map<String, String>> resultMaps = new LinkedList<Map<String,String>>();
		
		try {
		
			// initialize position on data
			resultData.beforeFirst();

			// get number of keys to extract
			int keyNumber = keys.size();
			
			// scan all rows found
			while (resultData.next()) {
				
				
				// initialize map for current row
				Map<String, String> currRowData = new HashMap<String, String>();
				
				// extract current row's value
				for (int i  = 0 ; i < keyNumber ; ++i) {
					
					// add data to parameters map
					currRowData.put(keys.get(i), resultData.getString(keys.get(i)));
				}

				// add to current row's data to list
				resultMaps.add(currRowData);
			}

			// move back to initial position in result set
			resultData.beforeFirst();
			
			// return data
			return resultMaps;
			
		} catch (SQLException e) {
			
			e.printStackTrace(); // TODO
			
			// database error
			throw new DBException("Could not retrieve apatments- database error!");
		}

	}
	
	/**
	 * Closes connection. If an exception is thrown while trying to close the connection,
	 * throws DBexception with given message as error message
	 * @param connection- connection object to be closed
	 * @param errorMsg- error message
	 * @throws DBException- throws database exception there was a problem with the database connection
	 */
	private void closeConnection(Connection connection, String errorMsg) throws DBException {
		
		try {
		
			// check if could establish connection
			if (null == connection) {
				
				// connection error
				throw new DBException(errorMsg);
			}
					
			// close connection
			connection.close();
			
		} catch (SQLException e) {				
			// database error
			throw new DBException(errorMsg);
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {

		try {
			
			StoreDB db = new StoreDB();
			
			double price = db.getProductPrice("guitar");
			System.out.println(price);
			
		} catch (DBException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
